/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/03/15
 */
package vn.smartware.co.server.dao.initialize;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.commons.Const;
import vn.smartware.co.framework.bean.CommonDataDTO;
import vn.smartware.co.framework.bean.CommonItemBean;
import vn.smartware.co.framework.bean.MenuDTO;
import vn.smartware.co.framework.bean.AddressDTO;
import vn.smartware.co.framework.bean.UserDTO;
import vn.smartware.co.framework.bean.ProgramDTO;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.db.factory.BaseDAO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.framework.form.BaseForm;
import vn.smartware.co.java.net.commons.EncryptionUtils;
import vn.smartware.co.java.net.commons.Utils;
import vn.smartware.co.server.dao.factory.AbstractInitializeDAO;
import vn.smartware.co.server.sql.factory.SQLFactory;

/**
 * @author thuctt
 * InitializeDAO
 */
public class InitializeDAO extends BaseDAO implements AbstractInitializeDAO {

	private static Log log = LogFactory.getLog(InitializeDAO.class);
	
	/**
	 * get menu
	 * @param user 
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList<MenuDTO> getMenu(LoginDTO user, Connection conn) throws ServerException {
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ArrayList<MenuDTO> list = new ArrayList<MenuDTO>();
		try {
			manager.getMenuSQL(user, conn);
			List<CommonDataDTO> dataList = getDataList(manager.executeQuery());
			for (CommonDataDTO row : dataList) {
				MenuDTO dto = new MenuDTO();
				dto.setMenuId(row.getInteger("MENU_ID"));
				dto.setMenuNameProperty(Utils.toEmpty(row.getString("MENU_NAME")));
				dto.setRemark(Utils.toEmpty(row.getString("REMARK")));
				
				list.add(dto);
			}
			return list;
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * getAvailableMenu
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList<MenuDTO> getAvailableMenu(Connection conn) throws ServerException {
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ArrayList<MenuDTO> list = new ArrayList<MenuDTO>();
		try {
			manager.getAvailableMenuSQL(conn);
			List<CommonDataDTO> dataList = getDataList(manager.executeQuery());
			for (CommonDataDTO row : dataList) {
				MenuDTO dto = new MenuDTO();
				dto.setMenuId(row.getInteger("MENU_ID"));
				dto.setMenuNameProperty(Utils.toEmpty(row.getString("MENU_NAME")));
				dto.setRemark(Utils.toEmpty(row.getString("REMARK")));
				
				list.add(dto);
			}
			return list;
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * get program list
	 * 
	 * @param menu 
	 * @param user 
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList<ProgramDTO> getPrgList(MenuDTO menu, LoginDTO user, Connection conn) throws ServerException {
		
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ArrayList<ProgramDTO> list = new ArrayList<ProgramDTO>();
		try {
			manager.getPrgListSQL(menu, user, conn);
			List<CommonDataDTO> dataList = getDataList(manager.executeQuery());
			for (CommonDataDTO row : dataList) {
				ProgramDTO dto = new ProgramDTO();
				dto.setPrgId(row.getInteger("PRG_ID"));
				dto.setIndex(Utils.toEmpty(row.getString("PRG_IDX")));
				dto.setPrgClass(Utils.toEmpty(row.getString("CLASS_ID")));
				dto.setEnName(Utils.toEmpty(row.getString("EN_NAME")));
				dto.setVnName(Utils.toEmpty(row.getString("VN_NAME")));
				dto.setDspDivision(Utils.toEmpty(row.getString("DISPLAY_DIVISION")));
				dto.setPrgAuth(Utils.toEmpty(row.getString("PRG_AUTH")));
				
				list.add(dto);
			}
			return list;
			
		} catch (SQLException e) {
			log.error("InitializeDAO getPrgList SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("InitializeDAO getPrgList SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * getPrograms
	 * @param form
	 * @param user
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList<ProgramDTO> getPrograms(BaseForm form,
			LoginDTO user, Connection conn) throws ServerException {
		
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ArrayList<ProgramDTO> list = new ArrayList<ProgramDTO>();
		try {
			manager.getProgramsSQL(form, user, conn);
			List<CommonDataDTO> dataList = getDataList(manager.executeQuery());
			for (CommonDataDTO row : dataList) {
				ProgramDTO dto = new ProgramDTO();
				dto.setPrgId(row.getInteger("PRG_ID"));
				dto.setIndex(Utils.toEmpty(row.getString("PRG_IDX")));
				dto.setMenuId(row.getInteger("MENU_ID"));
				dto.setPrgClass(Utils.toEmpty(row.getString("CLASS_ID")));
				dto.setEnName(Utils.toEmpty(row.getString("EN_NAME")));
				dto.setVnName(Utils.toEmpty(row.getString("VN_NAME")));
				dto.setDspDivision(Utils.toEmpty(row.getString("DISPLAY_DIVISION")));
				dto.setDspDivisionName(Utils.toEmpty(row.getString("DISPLAY_DIVISION_NAME")));
				//
				list.add(dto);
			}
			return list;
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * get program list of current login user
	 * @param userId
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList<ProgramDTO> getAuthPrograms(String userId, Connection conn)
			throws ServerException {
		
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ArrayList<ProgramDTO> list = new ArrayList<ProgramDTO>();
		try {
			manager.getAuthProgramsSQL(userId, conn);
			List<CommonDataDTO> dataList = getDataList(manager.executeQuery());
			for (CommonDataDTO row : dataList) {
				ProgramDTO dto = new ProgramDTO();
				dto.setPrgId(row.getInteger("PRG_ID"));
				dto.setMenuId(row.getInteger("MENU_ID"));
				dto.setPrgClass(Utils.toEmpty(row.getString("CLASS_ID")));
				dto.setEnName(Utils.toEmpty(row.getString("EN_NAME")));
				dto.setVnName(Utils.toEmpty(row.getString("VN_NAME")));
				dto.setDspDivision(Utils.toEmpty(row.getString("DISPLAY_DIVISION")));
				dto.setDspDivisionName(Utils.toEmpty(row.getString("DISPLAY_DIVISION_NAME")));
				//
				list.add(dto);
			}
			return list;
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * register program
	 * @param prg
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void registerProgram(ProgramDTO prg, LoginDTO user, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.registerProgramSQL(prg, user, conn);
			count = manager.executeUpdate();
			log.info("registerProgram: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * get only menu
	 * @param user
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList<MenuDTO> getMenuListWithoutAuth(LoginDTO user, Connection conn)
			throws ServerException {
		
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ArrayList<MenuDTO> list = new ArrayList<MenuDTO>();
		try {
			manager.getMenuListWithoutAuthSQL(user, conn);
			List<CommonDataDTO> dataList = getDataList(manager.executeQuery());
			for (CommonDataDTO row : dataList) {
				MenuDTO dto = new MenuDTO();
				dto.setMenuId(row.getInteger("MENU_ID"));
				dto.setMenuNameProperty(Utils.toEmpty(row.getString("MENU_NAME")));
				dto.setRemark(Utils.toEmpty(row.getString("REMARK")));
				dto.setInsertYmd(Utils.toEmpty(row.getString("INSERT_DATE")));
				dto.setDeleteYmd(Utils.toEmpty(row.getString("DELETE_DATE")));
				//
				list.add(dto);
			}
			return list;
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException. ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * create new menu
	 * @param dto
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void createNewMenu(MenuDTO dto, LoginDTO user, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.createNewMenuSQL(dto, user, conn);
			count = manager.executeUpdate();
			log.info("createNewMenu: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("InitializeDAO createNewMenu SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("InitializeDAO createNewMenu SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * create menu authority
	 * @param dto
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void createMenuAuthority(MenuDTO dto, LoginDTO user, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.createMenuAuthoritySQL(dto, user, conn);
			count = manager.executeUpdate();
			log.info("createMenuAuthority: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("InitializeDAO createMenuAuthority SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("InitializeDAO createMenuAuthority SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * get max menu ID
	 * @param conn
	 * @return int
	 * @throws ServerException
	 */
	public int getMaxMenuId(Connection conn) throws ServerException {
		
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ResultSet rs = null;
		int maxID = -1;
		try {
			manager.getMaxMenuIdSQL(conn);
			rs = manager.executeQuery();
			if (rs.next()){
				maxID = rs.getInt("MAX_ID");
			}
			
			return maxID;
			
		} catch (SQLException e) {
			log.error("InitializeDAO getMaxMenuId SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("InitializeDAO getMaxMenuId SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * update menu
	 * @param dto
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void updateMenu(MenuDTO dto, LoginDTO user, Connection conn) throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.updateMenuSQL(dto, user, conn);
			count = manager.executeUpdate();
			log.info("updateMenu: " + count + " row updated.");
			
		} catch (SQLException e) {
			log.error("InitializeDAO updateMenu SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("InitializeDAO updateMenu SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * delete menu
	 * @param dto
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteMenu(MenuDTO dto, Connection conn) throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.deleteMenuSQL(dto, conn);
			count = manager.executeUpdate();
			log.info("deleteMenu: " + count + " row deleted.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * delete user
	 * @param dto
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteUser(UserDTO dto, Connection conn) throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.deleteUserSQL(dto, conn);
			count = manager.executeUpdate();
			log.info(count + " row deleted.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * delete authority
	 * @param dto
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteAuth(MenuDTO dto, Connection conn) throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.deleteAuthSQL(dto, conn);
			count = manager.executeUpdate();
			log.info("deleteAuth: " + count + " row deleted.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * delete authority of User
	 * @param dto
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteAuthOfUser(UserDTO dto, Connection conn) throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.deleteAuthOfUserSQL(dto, conn);
			count = manager.executeUpdate();
			log.info("deleteAuthOfUser: " + count + " row deleted.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * Clear all rows in table ACTIONLOG
	 * @param conn
	 * @throws ServerException
	 */
	public void clearLog(Connection conn) throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.clearLogSQL(conn);
			count = manager.executeUpdate();
			log.info("clearLog: " + count + " rows deleted.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * get initialize items data for combobox
	 * @param key
	 * @param getBlankFlg
	 * @param conn
	 * @return List<CommonItemBean>
	 * @throws ServerException
	 */
	public List<CommonItemBean> getComboInitItems(Map<Object, Object> keyMap,
			Connection conn) throws ServerException {
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		try {
			List<CommonItemBean> itemList = new ArrayList<CommonItemBean>();
			Boolean hasBlankFlag = (Boolean) keyMap.get(Const.COMBO_KEY.HAS_BLANK);
			if (hasBlankFlag) {
				itemList.add(new CommonItemBean(Const.BLANK, Const.BLANK));
			}
			
			String keySearch = (String) keyMap.get(Const.COMBO_KEY.KEY);
			manager.getComboInitItemsSQL(keySearch, conn);
			List<CommonDataDTO> dataList = getDataList(manager);
			for (CommonDataDTO data : dataList) {
				CommonItemBean item = new CommonItemBean(data.getString("VALUE1"), data.getString("SEQ"));
				itemList.add(item);
			}
			
			return itemList;
		} 
		catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} 
		finally {
			try {
				manager.close();
			} 
			catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * getUsers
	 * @param form
	 * @param user
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList<UserDTO> getUsers(BaseForm form,
			LoginDTO user, Connection conn) throws ServerException {
		
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ArrayList<UserDTO> list = new ArrayList<UserDTO>();
		try {
			manager.getUsersSQL(form, user, conn);
			List<CommonDataDTO> dataList = getDataList(manager.executeQuery());
			for (CommonDataDTO dataRow : dataList) {
				EncryptionUtils encryptor = EncryptionUtils.getInstance();
				UserDTO dto = new UserDTO();
				dto.setUserId(dataRow.getInteger("USER_ID"));
				dto.setUserName(Utils.toEmpty(dataRow.getString("USER_NAME")));
				String pass = encryptor.decrypt(Utils.toEmpty(dataRow.getString("PASSWORD")));
				dto.setPassword(pass);
				dto.setLockoutDate(dataRow.getDate("LOCK_OUT_DATE"));
//				dto.setUserCd(Utils.toEmpty(dataRow.getString("USER_CD")));
				dto.setName(Utils.toEmpty(dataRow.getString("FULL_NAME")));
				dto.setBirthday(dataRow.getDate("BIRTHDAY"));
				dto.setIdNumber(Utils.toEmpty(dataRow.getString("ID_NUMBER")));
				dto.setTel(Utils.toEmpty(dataRow.getString("TEL")));
				dto.setJobCd(Utils.toEmpty(dataRow.getString("JOB_CD")));
				dto.setJobName(Utils.toEmpty(dataRow.getString("JOB_NAME")));
				dto.setMail(Utils.toEmpty(dataRow.getString("EMAIL")));
				//
				String addressCd = dataRow.getString("ADDRESS_ID");
				if (!Utils.isNullEmpty(addressCd)){
					AddressDTO addr = new AddressDTO();
					addr.setAddressCd(dataRow.getInteger("ADDRESS_ID"));
					addr.setHouseNo(Utils.toEmpty(dataRow.getString("HOUSE_NO")));
					addr.setStreet(Utils.toEmpty(dataRow.getString("STREET")));
					addr.setWard(Utils.toEmpty(dataRow.getString("WARD")));
					addr.setDistrict(Utils.toEmpty(dataRow.getString("DISTRICT")));
					addr.setCity(Utils.toEmpty(dataRow.getString("CITY")));
					addr.setNationality(Utils.toEmpty(dataRow.getString("NATIONALITY")));
					//
					dto.setAddress(addr);
				}
				list.add(dto);
			}
			return list;
		} 
		catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} 
		finally {
			try {
				manager.close();
			}
			catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * register new user
	 * @param dto 
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void registerLoginInfo(UserDTO dto, LoginDTO user, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.registerLoginInfoSQL(dto, user, conn);
			count = manager.executeUpdate();
			log.info("registerLoginInfo: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * update user
	 * @param dto 
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void updateLoginUser(UserDTO dto, LoginDTO user, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.updateLoginInfoSQL(dto, user, conn);
			count = manager.executeUpdate();
			log.info("updateLoginUser: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * register new user
	 * @param addr 
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void insertAddress(AddressDTO addr, LoginDTO user,
			Connection conn) throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.insertAddressSQL(addr, user, conn);
			count = manager.executeUpdate();
			log.info("insertAddress: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * update address
	 * @param addr 
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void updateAddress(AddressDTO addr, LoginDTO user, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.updateAddressSQL(addr, user, conn);
			count = manager.executeUpdate();
			log.info("updateAddress: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * get max menu ID
	 * @param addr 
	 * @param user 
	 * @param conn
	 * @return int
	 * @throws ServerException
	 */
	public int getMaxAddressCd(AddressDTO addr, LoginDTO user, Connection conn)
			throws ServerException {
		
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ResultSet rs = null;
		int maxID = -1;
		try {
			manager.getMaxAddressCdSQL(conn);
			rs = manager.executeQuery();
			if (rs.next()){
				maxID = rs.getInt("MAX_CD");
			}
			
			return maxID;
			
		} catch (SQLException e) {
			log.error("SQLException.", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException.", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * get max user ID
	 * @param conn
	 * @return int
	 * @throws ServerException
	 */
	public int getMaxUserID(Connection conn)
			throws ServerException {
		
		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		ResultSet rs = null;
		int maxID = -1;
		try {
			manager.getMaxUserIDSQL(conn);
			rs = manager.executeQuery();
			if (rs.next()){
				maxID = rs.getInt("MAX_ID");
			}
			
			return maxID;
			
		} catch (SQLException e) {
			log.error("SQLException.", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException.", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * deleteAllPrgsOfUser
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void deleteAllPrgsOfUser(BaseForm form, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.deleteAllPrgsOfUserSQL(form, conn);
			count = manager.executeUpdate();
			log.info("deleteAllPrgsOfUser: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * register auth
	 * @param prg
	 * @param userID 
	 * @param conn
	 * @throws ServerException
	 */
	public void registerAuth(ProgramDTO prg, String userID, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.registerAuthSQL(prg, userID, conn);
			count = manager.executeUpdate();
			log.info("registerAuth: " + count + " row registered.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	
	/**
	 * deleteAddress
	 * @param addr
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void deleteAddress(AddressDTO addr, Connection conn)
			throws ServerException {

		InitializeSQLManager manager = SQLFactory.createInitializeSQLManager();
		int count = 0;
		try {
			manager.deleteAddressSQL(addr, conn);
			count = manager.executeUpdate();
			log.info(count + " row deleted.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
			
		} finally {
			try {
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
}
